<?php
/**
* エラーチェッククラス
**/
require_once( '../system/Class_M_DAO.php' );
class Class_D_DNP_AREA extends Class_M_DAO {
	//protected properties begin
	/**
	 *
	 * テーブル名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_table  = "D_DNP_AREA";
	
	/**
	 *
	 * 主キー名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_pkey   = "id";
	
	/**
	 *
	 * ソート順
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	 protected $_order = "area_cd";
	 
	 protected $_cols       =  array(
		"pattern_cd"     => array(AOKI_VARTYPE_STR),
		"dist_id"        => array(AOKI_VARTYPE_STR),
		"area_nums"      => array(AOKI_VARTYPE_STR),
		"hand_cd"        => array(AOKI_VARTYPE_STR),
		"center_flg"     => array(AOKI_VARTYPE_STR)
	 );
	
	 protected $_checks     =  array(
		"pattern_cd"        => array( "EXIST","NUM" ),
		"dist_id"           => array( "NUM" ),
		"area_nums"         => array( "EXIST","NUM" ),
		"hand_cd"           => array( "NUM" ),
	 );
	 
	 //maxlength 
	 protected $_maxlens  = array( );
	 /*
 	//同じ新聞の都心合計数
	 public function getcentercountbyid( $data ) {
		foreach ($data as $v) {
			$idarr[$v['pattern_cd']] = "select {$v['id']} as id,{$v['pattern_cd']} as p" ;
		}
		$strUnionSelect = implode(" union all ", $idarr);
		
		
		$sqldnpFrom = " FROM D_DNP_AREA aa inner join ({$strUnionSelect}) bb on aa.pattern_cd = b.p " .
				" WHERE pattern_cd = '{$pattern_cd}' and  center_flg =1  ";
		$sqldnp = "SELECT sum(area_nums) as c_area_nums   " .$sqldnpFrom;
		$this->_db->query( $sqldnp );
		$retdata  = array();
		if(  $this->_db->num_rows() ){
			if ( $row = $this->_db->fetch() ){
				$retdata["c_area_nums"] = empty($row["c_area_nums"]) ? 0 : $row["c_area_nums"];
			} 
		}else {
			return false;
		}
		
		$sql = " select sum(a.shop_per) as c_shop_per , sum(a.shop_nums) as c_shop_nums " .
				" from  D_DNP_AREA_SHOP a inner join " .
				" ( SELECT id {$sqldnpFrom} ) b on a.dnpid = b.id ";
		
		$this->_db->query( $sql );
		if( $row = $this->_db->num_rows() ){
			if ( $row = $this->_db->fetch() ){
				$retdata["c_shop_per"]  = empty($row["c_shop_per"])  ? 0 : $row["c_shop_per"]; 
				$retdata["c_shop_nums"] = empty($row["c_shop_nums"]) ? 0 : $row["c_shop_nums"]; 
			}
		}else{
			return  FALSE;
		}
		
		return $retdata;
	 			
	 }
	 */
	 
	 //同じ新聞の都心合計数
	 public function getcentercount( $pattern_cd ) {
		$sqldnpFrom = " FROM D_DNP_AREA WHERE pattern_cd = '{$pattern_cd}' and  center_flg =1 ";
	 	$sqldnp = "SELECT sum(area_nums) as c_area_nums   " .$sqldnpFrom;
	 	$this->_db->query( $sqldnp );
	 	$retdata  = array();
	 	if(  $this->_db->num_rows() ){
	 		if ( $row = $this->_db->fetch() ){
				$retdata["c_area_nums"] = empty($row["c_area_nums"]) ? 0 : $row["c_area_nums"];
			} 
	 	}else {
	 		return false;
	 	}
	 	$sql = " select sum(a.shop_per) as c_shop_per , sum(a.shop_nums) as c_shop_nums,count(*) as c_shop_count " .
	 			" from  D_DNP_AREA_SHOP a inner join " .
	 			" ( SELECT id {$sqldnpFrom} ) b on a.dnpid = b.id ";
	 	
	 	$this->_db->query( $sql );
		if( $row = $this->_db->num_rows() ){
			if ( $row = $this->_db->fetch() ){
				$retdata["c_shop_per"]  = empty($row["c_shop_per"])  ? 0 : $row["c_shop_per"]; 
				$retdata["c_shop_nums"] = empty($row["c_shop_nums"]) ? 0 : $row["c_shop_nums"]; 
				$retdata["c_shop_count"] = empty($row["c_shop_count"]) ? 0 : $row["c_shop_count"];
			}
		}else{
			return  FALSE;
		}
		
		return $retdata;
	 			
	 }
	
	public function updateCenterShops($pattern, $centerSum) {
		$sql = "  update D_DNP_AREA_SHOP,D_DNP_AREA set D_DNP_AREA_SHOP.shop_nums  = round( D_DNP_AREA_SHOP.shop_per * {$centerSum} /100)" .
				" where  D_DNP_AREA_SHOP.dnpid = D_DNP_AREA.id " .
				" and  D_DNP_AREA.center_flg = '1'  and D_DNP_AREA.pattern_cd ={$pattern} ";
		return $this->exec_sql($sql) ;
		 
	}
	 public function search($where, &$page = NULL){
	 	$jointable       = " select a.dnpid,count(*) as shop_count,sum(a.shop_nums) as shop_nums,sum(a.shop_per) as shop_per,max(a.u_time) as au_time,max(a.u_user) as au_user  from D_DNP_AREA_SHOP a "; 

		//if (!empty( $shop_name )) {
		//	$jointableWhere  = " inner join M_SHOP b on a.shop_cd = b.shop_cd WHERE b.shop_name like '%{$shop_name}%' group by a.dnpid ";
		//	$fromtable = " FROM {$this->_table}  a inner  join ({$jointable} {$jointableWhere}) b on a.id = b.dnpid " ;
		//} else {
			$jointableWhere  = "  inner join M_SHOP b on a.shop_cd = b.shop_cd group by a.dnpid ";
			$fromtable = " FROM {$this->_table}  a left  join ({$jointable} {$jointableWhere}) b on a.id = b.dnpid " ;
	//	}
		$fromtable = $fromtable." inner join M_HAND h on h.hand_cd = a.hand_cd ";
		$sql = "SELECT a.*, h.hand_name, b.shop_count, b.shop_nums,b.shop_per,b.au_time, (select  user_name FROM M_LOGIN where id  = a.u_user) as m_user_name,(select  user_name FROM M_LOGIN where id  = b.au_user) as m_auser_name " .$fromtable;
		
		//$sqlcnt = "SELECT count(*) as cnt ".$fromtable;
		$orderby  =  "a.id";
		$pagenull =  NULL ;
		return  parent::search($where ,$pagenull ,$sql, NULL, $orderby);
	}
	
	/*
	 public function searchcsv($where, $shop_name, $flg = NULL ){
	 	$whereshopname = "";
	 	 if  (!empty( $shop_name )) {
	 	 	$whereshopname =   "AND sp.shop_name like '%{$shop_name}%' ";
	 	 }
	 	 if (empty($flg)) {
	 	  	$sql = "SELECT  b.*,a.matrix_cd,a.area_cd, a.area_name,a.publish_area_name, a.hand_name,a.area_nums, a.media_name, a.memo,m.flier_date,m.year4,m.week_cd, sp.shop_name," .
	 		"(select  user_name FROM M_LOGIN where id  = b.u_user) as m_user_name " ;
	 	 	$sql = $sql."  FROM {$this->_table}  a inner  join D_DNP_AREA_SHOP b on a.id = b.dnpid " .
	 		" inner join M_SHOP sp on b.shop_cd = sp.shop_cd {$whereshopname}".
	 		" inner join M_MATRIX_TERM m on a.matrix_cd = m.matrix_cd "; 
	 	 }elseif ($flg == "b") {
	 		$sql = "SELECT  a.matrix_cd,a.area_cd, a.area_name,a.publish_area_name, a.hand_name,a.area_nums, a.media_name, a.memo,m.flier_date,m.year4,m.week_cd" ;
	 		$sql = $sql."  FROM {$this->_table}  a left join D_DNP_AREA_SHOP b on a.id = b.dnpid " ;
	 		if  (!empty( $shop_name )) {
	 			$sql = $sql." inner join M_SHOP sp on b.shop_cd = sp.shop_cd {$whereshopname}";
	 		}
	 		$sql = $sql." inner join M_MATRIX_TERM m on a.matrix_cd = m.matrix_cd "; 
	 	 }elseif ($flg == "d") {
	 		$sql = "SELECT  b.*,a.matrix_cd,a.area_cd, a.area_name,a.publish_area_name, a.hand_name,a.area_nums, a.media_name, a.memo,m.flier_date,m.year4,m.week_cd, sp.shop_name," .
	 		"(select  user_name FROM M_LOGIN where id  = b.u_user) as m_user_name ";
	 	 	$sql = $sql."  FROM {$this->_table}  a inner  join D_DNP_AREA_SHOP b on a.id = b.dnpid " .
	 		" inner join M_SHOP sp on b.shop_cd = sp.shop_cd {$whereshopname}".
	 		" inner join M_MATRIX_TERM m on a.matrix_cd = m.matrix_cd "; 
	 	 }
	 	 
		$pagenull = NULL;
	 	if  ($flg == "b") {
	 		$sql = $sql." where ".implode(' AND ', $where);
	 		$sql = $sql." group by a.id ";
	 		return  parent::search(array() ,$pagenull ,$sql);
	 	}else {
	 		//$sql = $sql." group by a.id ";
	 		return  parent::search($where ,$pagenull ,$sql);
	 	}
	}
	*/
	/**
	 *
	 * 単一レコードを取得する
	 *
	 * @access    public
	 * @param     mixed     $id   主キーの値
	 * @return    mixed     取得に失敗した場合にFALSE, 成功した場合にレコードの内容(array)
	 */
	public function getByAreaAdShopCd($value) {
	    $fromtable = " FROM D_DNP_AREA_SHOP " ;
		$where     = " WHERE a.dnpid = '{$value}'" ;
		
		$sql = "SELECT * " .$fromtable.$where;
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
		}else{
			return  FALSE;
		}
	}
	
	public function getShopCount($value) {
		$sql = "SELECT count(*)  as cnt,sum(shop_nums) as nums FROM D_DNP_AREA_SHOP WHERE dnpid = '{$value}'";
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			return $this->_db->fetch();
			
		}else{
			return  null;
		}
	}
	
	public function getShops($value) {
		$sql = " SELECT  a.shop_cd, a.shop_per, a.shop_nums, b.shop_name,b.year_sale  FROM D_DNP_AREA_SHOP a " .
			" inner join  M_SHOP b on a.shop_cd = b.shop_cd  WHERE a.dnpid = '{$value}' ";
		$this->_db->query( $sql );
		
		if( $this->_db->num_rows() ){
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
		}else{
			return  FALSE;
		}
		
	}
	public function updateShopNums($area_cd, $nums) {
		
		$sql = "update  D_DNP_AREA_SHOP set shop_nums  = round( shop_per * {$nums} /100)   WHERE dnpid = '{$area_cd}'  " ;
		return $this->exec_sql($sql) ;
	}
	
	public function delAll($id){
		$executeflg = true;
		$dao = $this;
		try {
			$dao->begin_trans();
			$sql = "delete from  D_DNP_AREA  WHERE  id = '{$id}'  " ;
			$executeflg  = $this->exec_sql($sql) ;
			if ($executeflg) {
				$sql = "delete from  D_DNP_AREA_SHOP  WHERE dnpid = '{$id}'  " ;
				$executeflg  = $this->exec_sql($sql) ;
			}
			if ($executeflg) {
				$this->commit_trans();
				return true;
			} else {
				$this->rollback_trans();
				return false;
			}
		} catch (Exception $e) {
				$dao->rollback_trans();
				return false;
		}
		
	}
}

?>